package db;
import java.sql.*;
import java.util.*;
import beans.*;

public class signDAO extends ConnBean{
	private Connection connection=null;
	public List<signBean> getAllRecords(){
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <signBean> list=new ArrayList<signBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_sign";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				signBean sign=new signBean();
				sign.setId(rs.getInt(1));
				sign.setPid(rs.getString(2));
				sign.setIndid(rs.getInt(3));
				sign.setState(rs.getString(4));
				list.add(sign);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
	public signBean getRecord(int signid){
		ResultSet rs=null;
		Statement stmt=null;
		signBean sign=null;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_sign where id='"+signid+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				System.out.println("right");
				sign=new signBean();
				sign.setId(rs.getInt(1));
				sign.setPid(rs.getString(2));
				sign.setIndid(rs.getInt(3));
				sign.setState(rs.getString(4));
			}
			else
			{	
				System.out.println("null");
				sign=null;
			}
			stmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return sign;
  }	
	
	public boolean insertRecord(signBean record)
	{
		PreparedStatement pstmt=null;
		String sql="insert into YY_sign values(?,?,?,?)";
		if(record==null) 
			return false;
		boolean result=false;
		int number=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, record.getId());
			pstmt.setString(2, record.getPid());
			pstmt.setInt(3, record.getIndid());
			pstmt.setString(4, record.getState());
			number=pstmt.executeUpdate();
			pstmt.close();						
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(number>0)
			result=true;
		return result;
	}

	public boolean deleteRecord(signBean record)
	{
		PreparedStatement pstmt=null;
		String sql="delete from YY_sign where id=?";
		
		if(record==null) 
			return false;
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, record.getId());
			result=pstmt.executeUpdate();
			System.out.println(record.getId());
			pstmt.close();						
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public boolean updateRecord(signBean beforeRecord,signBean afterRecord)
	{
		if(beforeRecord==null || afterRecord==null) 
			return false;
		PreparedStatement pstmt=null;
		String sql="update YY_sign set id=?,pid=?,indid=?,state=?"+"where id="+beforeRecord.getId();
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, afterRecord.getId());
			pstmt.setString(2, afterRecord.getPid());
			pstmt.setInt(3, afterRecord.getIndid());
			pstmt.setString(4, afterRecord.getState());
			result=pstmt.executeUpdate();
			pstmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
}
